import logging
import jaydebeapi
import pandas as pd 

class DbService(object):
    def __init__(self,conn_str):
        self.conn_str = conn_str 

    def get_instance(self,ds_name):
        if ds_name == "gmrmbatch":
            return GaussDatabase(self.conn_str)
        else:
            return None 

class GaussDatabase(object):
    def __init__(self,gauss_conn_str):
        self.logger=logging.getLogger("Database")
        self.conn_str=gauss_conn_str
        self.conn=None 
    
    def connect(self):
        try:
            self.conn=jaydebeapi.connect(
                self.conn_str[0],
                self.conn_str[1],
                {"user":self.conn_str[2],"password":self_conn_str[3]},
                self.conn_str[4]
            )
            self.logger.info("Database connected")
        except Exception:
            self.logger.exception("Error in establish database connection")

    def commit(self):
        """
        手动提交
        :return:
        """
        self.conn.commit()

    def rollback(self):
        """
        手动回滚
        """
        self.conn.rollback()

    def __enter__(self):
        self.connect()
        return True 
    
    def get_dataframe_by_sql(self,sql):
        with self.conn.cursor() as cursor:
            self.logger.debug("QUery SQL [ {} ]".format(sql))
            cursor.execute(sql)
            rows=cursor.fetchall()
            col_columns=[row[0].upper() for row in cursor.description]
            df=pd.DataFrame(data=rows,columns=col_columns)
            return df 
        
    def exec_update_by_sql(self,sql,auto_commit=False):
        update_rows=0
        with self.conn.cursor() as cursor:
            self.logger.debug("Exec SQL [ {} ]".format(sql))
            cursor.execute(sql)
            update_rows=cursor.rowcount
            if auto_commit:
                self.conn.commit()
        return update_rows